/**
 *  EKology - An Ecosystem Simulation Framework
 *  Copyright (C) 2008  Aarón Tavío - aaron.tavio at gmail.com - ULPGC
 *
 *  This program is free software: you can redistribute it and/or modify
 *  it under the terms of the GNU General Public License as published by
 *  the Free Software Foundation, either version 3 of the License, or
 *  (at your option) any later version.
 *
 *  This program is distributed in the hope that it will be useful,
 *  but WITHOUT ANY WARRANTY; without even the implied warranty of
 *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *  GNU General Public License for more details.
 *
 *  You should have received a copy of the GNU General Public License
 *  along with this program.  If not, see <http://www.gnu.org/licenses/>.
 * 
 */

package net.ekology.core;

import net.ekology.core.datatypes.*;

import java.util.Calendar;
import java.util.GregorianCalendar;
import java.util.Vector;
import java.io.*;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSet;


/**
 * A MySQL database manager
 * <p>
 * Stablishes a layer between the database and the <code>Monitor</code>
 * 
 * @author Aarón Tavío - aaron.tavio at gmail.com
 * @version 1.0.0 - 20081019-1530
 * 
 * @see Monitor
 */
public class DatabaseManager implements Serializable {
    private String sURL ,sUser, sPassword;
    transient private Connection oConnection;
    
    private static final String sGetProjectID = "SELECT id FROM project WHERE name='%s' LIMIT 1;";
    private static final String sGetProject = "SELECT * FROM project WHERE id='%d' LIMIT 1;";
    private static final String sSetProject = "UPDATE project SET notes='%s' WHERE name='%s' LIMIT 1;";
    private static final String sAddProject = "INSERT INTO project(name,notes)  VALUES('%s','%s');";

    private static final String sGetSessionID = "SELECT id FROM session WHERE name='%s' AND project_id IN (SELECT id FROM project WHERE name='%s') LIMIT 1;";
    private static final String sGetSession = "SELECT * FROM session WHERE id='%d' LIMIT 1;";
    private static final String sSetSession = "UPDATE session SET notes='%s' WHERE id='%d' LIMIT 1;";
    private static final String sAddSession = "INSERT INTO session(project_id,name,notes)  VALUES('%d','%s','%s');";
        
    private static final String sGetSignalID = "SELECT id FROM signal WHERE name='%s' AND session_id IN (SELECT id FROM session WHERE name='%s' AND project_id IN (SELECT id FROM project WHERE name='%s')) LIMIT 1;";
    private static final String sGetSignal = "SELECT * FROM signal WHERE id='%d' LIMIT 1;";
    private static final String sAddSignal = "INSERT INTO signal(session_id,name,type)  VALUES('%d','%s','%s');";
    
    private static final String sGetValues = "SELECT * FROM value WHERE signal_id='%d';";
    private static final String sGetActualValue = "SELECT * FROM `%s` WHERE value_id='%d' LIMIT 1;";
    private static final String sAddValue = "INSERT INTO value(signal_id,ektime) VALUES(%d,FROM_UNIXTIME(%d));";
    private static final String sAddActualValue = "INSERT INTO `%s`(value_id,value) VALUES(%d,%s);";
    
    private static final String sGetLastID = "SELECT LAST_INSERT_ID() AS id;";
    
    
    
    public DatabaseManager(String sServer, String sDatabase, String sUser, String sPassword) {
        this.sUser = sUser;
        this.sPassword = sPassword;
        oConnection = null;
        
        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            sURL = String.format("jdbc:mysql://%s/%s",sServer,sDatabase);
        } catch(Exception e) {e.printStackTrace();}
    }
    
    
    public boolean connect() {
        if (oConnection == null) {
            try {
                oConnection = DriverManager.getConnection(sURL,sUser,sPassword);
            } catch (SQLException e) {
                printSQLException(e);
                oConnection = null;
            }
        }
        
        return isConnected();
    }
    
    
    public boolean isConnected() {
        return (oConnection==null)? false : true ;
    }
    
    
    /**
     * Performs a select SQL query
     * 
     * @param sQuery a SQL select query
     * @return a <code>ResultSet</code> object that contains the data produced by the given query; never <code>null</code>
     */
    private ResultSet doSelect(String sQuery) {
        ResultSet oResult;
        Statement oStatement;
        
        oResult = null;
        
        try {
            oStatement = oConnection.createStatement();
            oResult = oStatement.executeQuery(sQuery);
        } catch (SQLException e) {
            printSQLException(e);
        }
        
        return oResult;
    }
    
    
    /**
     * Performs an update SQL query
     * 
     * @param sQuery a SQL update query 
     * @return the number of affected rows or -1 if an error is found
     */
    private int doUpdate(String sQuery) {
        int iResult;
        Statement oStatement;
        
        iResult = -1;
        try {
            oStatement = oConnection.createStatement();
            iResult = oStatement.executeUpdate(sQuery);
        } catch (SQLException e) {
            printSQLException(e);
        }
        
        return iResult;
    }
    
    
    public int getProjectID(String sName) {
        ResultSet oRS;
        int iResult;
        
        iResult = -1;
        oRS = doSelect(String.format(sGetProjectID, sName));
        try {
            oRS.first();
            iResult = oRS.getInt("id");
        } catch(Exception ignore) {}
        
        return iResult;
    }
    
    public boolean hasProject(int iProjectID) {
        ResultSet oResultSet;
        boolean bResult;
        
        oResultSet = doSelect(String.format(sGetProject, iProjectID));
        try {
            bResult = oResultSet.next();
        } catch(Exception e) {
            bResult = false;
        }
        
        return bResult;
    }
    
    public ProjectData getProject(int iProjectID) {
        ResultSet oRS;
        ProjectData oResult;
        Calendar oCreationDate;
        
        oResult = null;
        oRS = doSelect(String.format(sGetProject, iProjectID));
        try {
            oRS.first();
            oCreationDate = new GregorianCalendar();
            oCreationDate.setTimeInMillis(oRS.getLong("creation_date"));
            oResult = new ProjectData(oRS.getString("name"),oRS.getString("notes"),oCreationDate);
        } catch(Exception ignore) {}
        
        return oResult;
    }
    
    
    public void setProject(ProjectData oProject) {
        doUpdate(String.format(sSetProject, oProject.sNotes, oProject.sName));
    }
    
    
    public void addProject(ProjectData oProject) {
        doUpdate(String.format(sAddProject, oProject.sName, oProject.sNotes));
    }
    
    
    public int getSessionID(String sProject, String sSession) {
        int iResult;
        ResultSet oRS;
        
        iResult = 0;
        oRS = doSelect(String.format(sGetSessionID, sSession, sProject));
        try {
            oRS.first();
            iResult = oRS.getInt("id");
        } catch(Exception ignore) {}
        
        return iResult;
    }
    
    
    public boolean hasSession(int iSessionID) {
        ResultSet oResultSet;
        boolean bResult;
        
        oResultSet = doSelect(String.format(sGetSession, iSessionID));
        try {
            bResult = oResultSet.next();
        } catch(Exception e) {
            bResult = false;
        }
        
        return bResult;
    }
    
    
    public SessionData getSession(int iSessionID) {
        ResultSet oRS;
        SessionData oResult;
        Calendar oCreationDate;
        
        oResult = null;
        oRS = doSelect(String.format(sGetSession, iSessionID));
        try {
            oRS.first();
            oCreationDate = new GregorianCalendar();
            oCreationDate.setTimeInMillis(oRS.getLong("creation_date"));
            oResult = new SessionData(oRS.getString("name"),oRS.getString("notes"),oCreationDate);
        } catch(Exception ignore) {}
        
        return oResult;
    }
    
    
    public void setSession(int iSessionID, SessionData oSession) {
        doUpdate(String.format(sSetSession, oSession.sNotes, iSessionID));
    }
    
    
    public void addSession(int iProjectID, SessionData oSession) {
        doUpdate(String.format(sAddSession, iProjectID, oSession.sName, oSession.sNotes));
    }
    
    
    public int getSignalID(String sProject, String sSession, String sSignal) {
        int iResult;
        ResultSet oRS;
        
        iResult = 0;
        oRS = doSelect(String.format(sGetSignalID, sSignal, sSession, sProject));
        try {
            oRS.first();
            iResult = oRS.getInt("id");
        } catch(Exception ignore) {}
        
        return iResult;
    }
    
    
    public boolean hasSignal(int iSignalID) {
        ResultSet oResultSet;
        boolean bResult;
        
        oResultSet = doSelect(String.format(sGetSignal, iSignalID));
        try {
            bResult = oResultSet.next();
        } catch(Exception e) {
            bResult = false;
        }
        
        return bResult;
    }
    
    
    public SignalData getSignal(int iSignalID) {
        ResultSet oRS;
        SignalData oResult;
        
        oResult = null;
        oRS = doSelect(String.format(sGetSignal, iSignalID));
        try {
            oRS.first();
            oResult = new SignalData(oRS.getString("name"),oRS.getString("type"));
        } catch(Exception ignore) {}
        
        return oResult;
    }
    
    
    public void addSignal(int iSessionID, SignalData oSignal) {
        doUpdate(String.format(sAddSignal, iSessionID, oSignal.sName, oSignal.oType.toString().toLowerCase()));
    }
    
    
    public ValueData[] getValues(int iSignalID) {
        String sSignal;
        String sType;
        ResultSet oRS,oValueRS;
        ValueData[] aResult;
        Vector<ValueData> vValue;
        Calendar oEKTime;
        Object oValue;
        
        aResult = null;
        vValue = new Vector<ValueData>();
        oRS = doSelect(String.format(sGetSignal, iSignalID));
        try {
            oRS.first();
            sSignal = oRS.getString("name");
            sType = oRS.getString("type");
            
            oRS = doSelect(String.format(sGetValues, iSignalID));
            while(oRS.next()) {
                oEKTime = new GregorianCalendar();
                oEKTime.setTimeInMillis(oRS.getTimestamp("ektime").getTime());
                oValueRS = doSelect(String.format(sGetActualValue, sType, oRS.getInt("id")));
                oValueRS.first();
                oValue = oValueRS.getObject("value");
                vValue.add(new ValueData(sSignal,oEKTime,oValue));
            }
        } catch(SQLException ignore) {printSQLException(ignore);}
        
        aResult = new ValueData[vValue.size()];
        vValue.toArray(aResult);
        
        return aResult;
    }
    
    
    public void addValue(int iSignalID, ValueData oValue) {
        int iValueID;
        SignalData oSignal;

        oSignal = getSignal(iSignalID);
        doUpdate(String.format(sAddValue, iSignalID, Math.round(oValue.oEKTime.getTimeInMillis()/1000.0)));
        iValueID = getLastID();
        doUpdate(String.format(sAddActualValue, oSignal.oType.toString().toLowerCase(), iValueID, oValue.oValue.toString()));
    }
    
    
    public int getLastID() {
        ResultSet oRS;
        int iResult;
        
        iResult = -1;
        oRS = doSelect(sGetLastID);
        try {
            oRS.first();
            iResult = oRS.getInt("id");
        } catch(Exception ignore) {}
        
        return iResult;
    }
    
    
    public void disconnect() {
        if (oConnection!=null) {
            try {
                oConnection.close();
                oConnection = null;
            } catch (SQLException e) {
                printSQLException(e);
            }
        }
    }
    
    
    private static int getRowCount(ResultSet oResultSet) {
        int iResult,iCurrRow;
        
        iResult = 0;
        try {
            iCurrRow = oResultSet.getRow();
            oResultSet.last();
            iResult = oResultSet.getRow();
            if (iCurrRow == 0) oResultSet.beforeFirst();
            else oResultSet.absolute(iCurrRow);
        } catch(Exception ignore) {ignore.printStackTrace();}

        return iResult;
    }
    
    
    private void printSQLException(SQLException e) {
        System.out.println("SQLException: " + e.getMessage());
        System.out.println("SQLState: " + e.getSQLState());
        System.out.println("VendorError: " + e.getErrorCode());
    }
    
    
    private void readObject(ObjectInputStream in) throws IOException, ClassNotFoundException {
        in.defaultReadObject();
        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
        } catch(Exception e) {e.printStackTrace();}
    }
    
    
}
